package sqlconnect;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.*;
public class DatabaseTest {
    public static void main(String[] args) {
        // 数据库连接信息
        String url = "jdbc:mysql://localhost:3306/sql_tt?serverTimezone=Asia/Shanghai";
        String username = "root";
        String password = "@21Gohandong";
        // 要执行的 SQL 语句数组
        String[] sqlStatements = {
                "DROP DATABASE IF EXISTS SQL_tt;",
                "CREATE DATABASE SQL_tt;",
                "USE SQL_tt;",
                "DROP TABLE IF EXISTS works_with;",
                "DROP TABLE IF EXISTS client;",
                "DROP TABLE IF EXISTS employee;",
                "DROP TABLE IF EXISTS branch;",
                "CREATE TABLE employee (emp_id INT PRIMARY KEY,name VARCHAR(20),birth_date DATE,sex VARCHAR(1),salary INT,branch_id INT,sup_id INT);",
                "CREATE TABLE branch (branch_id INT PRIMARY KEY,branch_name VARCHAR(20),manager_id INT,FOREIGN KEY (manager_id) REFERENCES employee(emp_id) ON DELETE SET NULL);",
                "ALTER TABLE employee ADD FOREIGN KEY (branch_id) REFERENCES branch(branch_id) ON DELETE SET NULL;",
                "ALTER TABLE employee ADD FOREIGN KEY (sup_id) REFERENCES employee(emp_id) ON DELETE SET NULL;",
                "CREATE TABLE client (client_id INT PRIMARY KEY,client_name VARCHAR(20),phone VARCHAR(20));",
                "CREATE TABLE works_with (emp_id INT,client_id INT,total_scales INT,PRIMARY KEY (emp_id, client_id),FOREIGN KEY (emp_id) REFERENCES employee(emp_id) ON DELETE CASCADE,FOREIGN KEY (client_id) REFERENCES client(client_id) ON DELETE CASCADE);",
                "INSERT INTO branch VALUES (1, '研发', NULL);",
                "INSERT INTO branch VALUES (2, '行政', NULL);",
                "INSERT INTO branch VALUES (3, '咨询', NULL);",
                "INSERT INTO employee VALUES (206, '小黄', '1998-10-08', 'F', 50000, 1, NULL);",
                "INSERT INTO employee VALUES (207, '小绿', '1985-09-16', 'M', 29000, 2, 206);",
                "INSERT INTO employee VALUES (208, '小黑', '2000-12-19', 'M', 35000, 3, 206);",
                "INSERT INTO employee VALUES (209, '小白', '1997-01-22', 'F', 39000, 3, 207);",
                "INSERT INTO employee VALUES (210, '小开', '1925-11-10', 'F', 84000, 1, 207);",
                "UPDATE branch SET manager_id = 208 WHERE branch_id = 3;",
                "INSERT INTO client VALUES (400, '阿狗', '254354335');",
                "INSERT INTO client VALUES (401, '阿猫', '25633889');",
                "INSERT INTO client VALUES (402, '旺财', '45354345');",
                "INSERT INTO client VALUES (403, '露西', '54354365');",
                "INSERT INTO client VALUES (404, '艾瑞克', '18783783');",
                "INSERT INTO works_with VALUES (206, 400, 70000);",
                "INSERT INTO works_with VALUES (207, 401, 24000);",
                "INSERT INTO works_with VALUES (208, 402, 9800);",
                "INSERT INTO works_with VALUES (208, 403, 24000);",
                "INSERT INTO works_with VALUES (210, 404, 87940);",
                "SELECT * FROM employee;",
                "SELECT * FROM client;",
                "SELECT * FROM `employee` ORDER BY `salary`;",
                "SELECT * FROM `employee` ORDER BY `salary` DESC LIMIT 3;",
                "SELECT `name` FROM `employee`;",
                "SELECT DISTINCT `branch_id` FROM `employee`;",
                "SELECT COUNT(`sup_id`) FROM `employee` WHERE `birth_date`> '1970-01-01' AND `sex`='F';",
                "SELECT AVG(`salary`) FROM  `employee`;",
                "SELECT SUM(`salary`) FROM `employee`;",
                "SELECT MAX(`salary`) FROM `employee`;",
                "SELECT * FROM `client` WHERE `phone` LIKE '%335';",
                "SELECT * FROM `client` WHERE `phone` LIKE '%354%';",
                "SELECT * FROM `client` WHERE `client_name` LIKE '艾%';",
                "SELECT * FROM employee WHERE birth_date LIKE '%12%';",
                "SELECT `name` FROM  `employee` UNION SELECT `client_name`  FROM `client` UNION SELECT `branch_name` FROM `branch`;",
                "SELECT `emp_id`,`name` FROM`employee` UNION SELECT `client_id`,`client_name`FROM`client`;",
                "INSERT INTO branch VALUES (4, '投降', NULL);",
                "SELECT * FROM `employee` JOIN `branch` ON `emp_id`=`manager_id`;",
                "SELECT `emp_id`,`name`,`branch_name` FROM `employee` JOIN `branch` ON `emp_id`=`manager_id`;",
                "SELECT `name` FROM `employee` WHERE `emp_id`=(SELECT `manager_id` FROM `branch` WHERE `branch_name` ='研发');",
                "SELECT `name` FROM `employee` WHERE `emp_id`IN(SELECT `emp_id` FROM `works_with` WHERE `total_scales`>50000);",
                "SELECT user FROM mysql.user;",
                "COMMIT;"
        };
        try {
            // 加载 JDBC 驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立数据库连接
            Connection connection = DriverManager.getConnection(url, username, password);
            System.out.println("数据库连接成功！");
            // 创建 Statement 对象
            Statement statement = connection.createStatement();
            for (String sql : sqlStatements) {
                System.out.println("正在执行 SQL: " + sql);
                // 判断是否是查询语句
                if (sql.trim().toUpperCase().startsWith("SELECT")) {
                    ResultSet resultSet = statement.executeQuery(sql);
                    // 处理结果集
                    ResultSetMetaData metaData = resultSet.getMetaData();
                    int columnCount = metaData.getColumnCount();
                    while (resultSet.next()) {
                        for (int i = 1; i <= columnCount; i++) {
                            System.out.print(metaData.getColumnName(i) + ": " + resultSet.getString(i) + "\t");
                        }
                        System.out.println();
                    }
                    resultSet.close();
                } else {
                    // 执行非查询语句
                    int affectedRows = statement.executeUpdate(sql);
                    System.out.println("影响的行数: " + affectedRows);
                }
            }
            // 关闭资源
            statement.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}